﻿using ConfExpert.Enums;
using DevTeam;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Windows.Forms;

namespace ConfExpert.Forms.Sistem.Conversion
{
    public static class DU4Yeni2
    {
        public static DataTable Convert(ProcessMethod Method, string Path)
        {
            //Assumptions:
            //Beden dağılımları 4., caption'lar 5., veriler 6+ sütunlarda
            //Satırlar okunmaya başladıktan sonra B sütununda değer okunmazsa o dosyanın işlenecek satırları bitmiş demektir
            //Beden boyları (label) tamsayıdır
            //Dağılımın erkek mi bayan mı olduğu MODEL numarasından bulunur (bkz. ilgili formül)

            List<string> types = new List<string>();

            DataTable dtSablon = new DataTable();

            foreach (string label in "32	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50".Split('\t'))
                dtSablon.Columns.Add("E" + label, typeof(int));

            foreach (string label in "32	34	36	38	40	42	44	46	48	50".Split('\t'))
                dtSablon.Columns.Add("B" + label, typeof(int));

            foreach (string label in "ST".Split('\t'))
                dtSablon.Columns.Add(label, typeof(int));

            dtSablon.Columns.Add("DosyaAdi", typeof(String));

            DataTable dtLabels = new DataTable();
            for (int i = 0; i<60; i++)
                dtLabels.Columns.Add("col" + i.ToString(), typeof(string));

            dtLabels.Rows.Add(dtLabels.NewRow());


            if (Method == ProcessMethod.XlsFolder)
                foreach (string fileName in Directory.GetFiles(Path, "*.xls*", SearchOption.AllDirectories))
                    ProcessOne(dtSablon, dtLabels, fileName);
            else
                ProcessOne(dtSablon, dtLabels, Path);

            return dtSablon;

            //gridControl1.DataSource = dtSablon;
            //gridControl1.ExportToXlsx("d:\\tt.xlsx");

        }

        private static void ProcessOne(DataTable dtSablon, DataTable dtLabels, string fileName)
        {
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(fileName);
            System.Data.DataSet dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
            DataTable dt = dataSet.Tables[1];

            string lb = string.Empty, cg = string.Empty, dd = string.Empty, ss = string.Empty, ft = string.Empty;
            DateTime ddate = new DateTime(2000, 1, 1);

            if (dt.Rows.Count > 5)
            {
                int captionrow = 0;
                for (int r = 0; r < dt.Rows.Count; r++)
                    if (dt.Rows[r][0].ToStr() == "PA.No.")
                    {
                        captionrow = r;
                        break;
                    }

                if (captionrow > 0)
                    for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                    {
                        string cellValue3 = dt.Rows[captionrow-2][colIndex].ToStr();
                        string cellValue4 = dt.Rows[captionrow-1][colIndex].ToStr();
                        string cellValue5 = dt.Rows[captionrow][colIndex].ToStr();

                        //if (cellValue5.Contains("substituation"))
                        //    cellValue5 = "Subst";

                        if (cellValue3 != string.Empty)
                            dtLabels.Rows[0][colIndex] = cellValue3;
                        
                        if (cellValue4 != string.Empty)
                        {
                            dtLabels.Rows[0][colIndex] = cellValue4;
                            if (!dtSablon.Columns.Contains(cellValue4))
                                dtSablon.Columns.Add(cellValue4, typeof(string));
                        }

                        if (cellValue5 != string.Empty)
                        {
                            dtLabels.Rows[0][colIndex] = cellValue5;
                            if (!dtSablon.Columns.Contains(cellValue5))
                                dtSablon.Columns.Add(cellValue5, typeof(string));
                        }
                    }

                for (int rowIndex = captionrow + 1; rowIndex < dt.Rows.Count; rowIndex++)
                {
                    bool bayan = false;
                    string label, cellValue;

                    bool foundEndLine = false;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        cellValue = dt.Rows[rowIndex][i].ToString().Replace(" ", "").ToLower();
                        if (cellValue.Contains("summe:"))
                        {
                            foundEndLine = true;
                            break;
                        }

                        label = dtLabels.Rows[0][i].ToString();
                        if (label == "Modell No.")
                            if (cellValue != string.Empty)
                                bayan = !char.IsNumber(cellValue[0]);
                    }

                    if (foundEndLine)
                        continue;


                    DataRow dr = dtSablon.NewRow();
                    dr["DosyaAdi"] = Path.GetFileName(fileName);
                    //dr["Etiket"] = lb;
                    //dr["CuttingGroup"] = cg;
                    //dr["Season"] = ss;
                    //dr["Termin"] = ddate;
                    //dr["Fit"] = ft;

                    //string ModellNo = dr["Modell No."].ToString();
                    //bool bayan = !char.IsNumber(ModellNo[0]);
                        
                    //foreach (char c in ModellNo.ToCharArray())
                    //    if (!char.IsNumber(c))
                    //        ModellNo = ModellNo.Replace(c.ToString(), "");
                    //bayan = (ModellNo.ToInt() / 10000) == 1;
                    
                    for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                    {
                        int dummy;
                        cellValue = dt.Rows[rowIndex][colIndex].ToStr();
                        label = dtLabels.Rows[0][colIndex].ToString();
                        if (int.TryParse(label, out dummy))
                            label = (bayan ? "B" : "E") + label;

                        if (label != string.Empty && cellValue != string.Empty)
                        {
                            if (label == "Colour")
                                dr[label] = cellValue.PadLeft(3, '0');
                            else if (label == "PA Date" || label == "Termin" || label.StartsWith("Delivery date"))
                            {
                                if (cellValue != "asap")
                                {
                                    string dateString = cellValue;
                                    if (IsValidDate(dateString))
                                        dr[label] = DateTime.Parse(dateString).ToString("dd.MM.yyyy");
                                    else
                                        dr[label] = DateTime.FromOADate(double.Parse(cellValue)).ToString("dd.MM.yyyy");

                                }
                            }
                            else if (!(label == "prod. price" && dr[label] != DBNull.Value))
                                dr[label] = cellValue;
                        }
                    }

                    if (dr["Order No."] != DBNull.Value)
                        dtSablon.Rows.Add(dr);
                }

            }


        }

        public static bool IsValidDate(string dateTime)
        {
            string[] formats = { "dd.MM.yyyy", "dd/MM/yyyy" };
            DateTime parsedDateTime;
            return DateTime.TryParseExact(dateTime, formats, Application.CurrentCulture,
                //new CultureInfo("en-US"), 
                DateTimeStyles.None, out parsedDateTime);
        }

    }
}
